{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Load statistics to CSV\n",
    "student_score_raw = pd.read_csv('Scores_Raw.csv', index_col=0)\n",
    "student_score_raw.index.names = ['Student ID']\n",
    "\n",
    "# Update column label\n",
    "student_score_raw.rename(columns={'Student Name: Student ID' : 'Student ID'}, inplace=True)\n",
    "student_score_raw.rename(columns={'Student Name: Student Name' : 'Student Name'}, inplace=True)\n",
    "\n",
    "# Add SAT, TOEFL Total\n",
    "student_score_raw['SAT Calculated Total'] = student_score_raw['Reading (SAT New)'] + student_score_raw['Math (SAT New)']\n",
    "student_score_raw['TOEFL Calculated Total'] = student_score_raw['Listening (TOEFL)'] + student_score_raw['Reading (TOEFL)'] + student_score_raw['Speaking (TOEFL)'] + student_score_raw['Writing (TOEFL)']\n",
    "student_score_raw['ACT Calculated Composit'] = np.mean([student_score_raw['English (ACT)'], student_score_raw['Math (ACT)'], student_score_raw['Reading (ACT)'], student_score_raw['Science (ACT)']])\n",
    "\n",
    "# Replace 0 with NaN for total value\n",
    "student_score_raw['SAT Calculated Total'].replace(0, np.nan, inplace=True)\n",
    "student_score_raw['TOEFL Calculated Total'].replace(0, np.nan, inplace=True)\n",
    "student_score_raw['ACT Calculated Composit'].replace(0, np.nan, inplace=True)\n",
    "\n",
    "# Save to FY16-17 Admission Result Cleaned.csv\n",
    "student_score_raw.to_csv('Scores Cleaned.csv', sep=',', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Grouped score by student\n",
    "student_score_raw_grouped = student_score_raw.groupby('Student ID')\n",
    "\n",
    "# Create new dataframe\n",
    "student_score_final = pd.DataFrame()\n",
    "\n",
    "student_score_final['Student Name'] = student_score_raw_grouped['Student Name'].max()\n",
    "\n",
    "student_score_final['TOEFL-Listening'] = student_score_raw_grouped['Listening (TOEFL)'].max()\n",
    "student_score_final['TOEFL-Reading'] = student_score_raw_grouped['Reading (TOEFL)'].max()\n",
    "student_score_final['TOEFL-Speaking'] = student_score_raw_grouped['Speaking (TOEFL)'].max()\n",
    "student_score_final['TOEFL-Writing'] = student_score_raw_grouped['Writing (TOEFL)'].max()\n",
    "student_score_final['TOEFL-Total'] = student_score_raw_grouped['TOEFL Calculated Total'].max()\n",
    "\n",
    "student_score_final['SAT-Reading'] = student_score_raw_grouped['Reading (SAT New)'].max()\n",
    "student_score_final['SAT-Math'] = student_score_raw_grouped['Math (SAT New)'].max()\n",
    "\n",
    "student_score_final['ACT-English'] = student_score_raw_grouped['English (ACT)'].max()\n",
    "student_score_final['ACT-Math'] = student_score_raw_grouped['Math (ACT)'].max()\n",
    "student_score_final['ACT-Reading'] = student_score_raw_grouped['Reading (ACT)'].max()\n",
    "student_score_final['ACT-Science'] = student_score_raw_grouped['Science (ACT)'].max()\n",
    "student_score_final['ACT-Writing'] = student_score_raw_grouped['Writing (ACT)'].max()\n",
    "\n",
    "def gpa_summary(df): \n",
    "    GPAs = \"\"\n",
    "    for i, row in df.iterrows():\n",
    "        if (GPAs != \"\"):\n",
    "            GPAs += \", \"\n",
    "        if (not np.isnan(row[\"GPA\"])):\n",
    "            if (row[\"Grade (GPA)\"] == \"Grade 09\"):\n",
    "                Grade = \"G09\"\n",
    "            elif (row[\"Grade (GPA)\"] == \"Grade 10\"):\n",
    "                Grade = \"G10\"\n",
    "            elif (row[\"Grade (GPA)\"] == \"Grade 11\"):\n",
    "                Grade = \"G11\"\n",
    "            elif (row[\"Grade (GPA)\"] == \"Grade 12\"):\n",
    "                Grade = \"G12\"\n",
    "            else:\n",
    "                Grade = \"G11\"\n",
    "            \n",
    "            GPAs += Grade + \": \" + str(row[\"GPA\"])\n",
    "            \n",
    "            if (not np.isnan(row[\"GPA Scale\"])):\n",
    "                GPAs += \"/\" + str(row[\"GPA Scale\"])\n",
    "            \n",
    "            if (row[\"Weighted (GPA)\"]):\n",
    "                GPAs += \" W\"        \n",
    "    return GPAs\n",
    "\n",
    "def score_summary(df, subject_col=\"\", score_col=\"\"): \n",
    "    Subjects = \"\"\n",
    "    for i, row in df.iterrows():\n",
    "        if (Subjects != \"\"):\n",
    "            Subjects += \", \"\n",
    "        Subjects += row[subject_col] + \": \" \n",
    "        if (np.isnan(row[score_col])):\n",
    "            Subjects += \"0\"\n",
    "        else:\n",
    "            Subjects += str(row[score_col])\n",
    "    return Subjects\n",
    "\n",
    "# Calculate GPA Summary\n",
    "student_score_raw_gpa = student_score_raw.loc[student_score_raw[\"Type\"] == \"GPA\", [\"GPA\", \"GPA Scale\", \"Weighted (GPA)\", \"Grade (GPA)\"]]\n",
    "student_score_raw_gpa['Grade (GPA)'].replace(np.nan, \"\", inplace=True)\n",
    "student_score_final['GPA-Summary'] = student_score_raw_gpa.groupby(\"Student ID\").apply(gpa_summary)\n",
    "\n",
    "# Calculate SAT2 Summary\n",
    "student_score_final['SAT2-Count'] = student_score_raw_grouped['Score (SAT2)'].count()\n",
    "student_score_final['SAT2-Mean'] = student_score_raw_grouped['Score (SAT2)'].mean()\n",
    "student_score_raw_sat2 = student_score_raw.loc[student_score_raw[\"Type\"] == \"SAT2\", [\"Subject (SAT2)\", \"Score (SAT2)\"]]\n",
    "student_score_final['SAT2-Summary'] = student_score_raw_sat2.groupby(\"Student ID\").apply(score_summary, subject_col=\"Subject (SAT2)\", score_col=\"Score (SAT2)\")\n",
    "\n",
    "# Calculate AP Summary\n",
    "student_score_final['AP-Count'] = student_score_raw_grouped['Score (AP)'].count()\n",
    "student_score_final['AP-Mean'] = student_score_raw_grouped['Score (AP)'].mean()\n",
    "student_score_raw_ap = student_score_raw.loc[student_score_raw[\"Type\"] == \"AP\", [\"Subject (AP)\", \"Score (AP)\"]]\n",
    "student_score_final['AP-Summary'] = student_score_raw_ap.groupby(\"Student ID\").apply(score_summary, subject_col=\"Subject (AP)\", score_col=\"Score (AP)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill NaN with 0\n",
    "student_score_final.fillna(0, inplace=True)\n",
    "\n",
    "# Save to FY16-17 Admission Result Cleaned.csv\n",
    "student_score_final.to_csv('Scores.csv', sep=',', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
